Snowflakeの「CREATE TABLE … USING TEMPLATE」構文を試してみた
こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。
先日、SnowflakeのPreview機能として公開された、半構造化データのカラム定義検出を試したのですが、その際にCREATE TABLE ... USING TEMPLATEの利用の仕方が分からずに試せていませんでした。
こちらについて、Mineaki Motohashiさん (@mmotohas)から情報を頂いたので早速試してみたいと思います。（いつもありがとうございます！
USING TEMPLATEを使ったテーブル作成はこんな感じで実行できますー。#SnowflakeDB
create or replace table region using template(
select array_agg(object_construct(*))
from table(infer_schema(
location => '@<stage_name>/<file_name>',
file_format => '<format_name>'
)));— Mineaki Motohashi (@mmotohas) June 14, 2021
前提条件
ユーザーステージの下記のパスに、今回利用するParquetファイルが配置済みとなります。
@~/unload/region.parquet
CREATE TABLE ... USING TEMPLATE 構文でテーブルを作成する
では、早速試してみます。
-- コンテキスト設定
USE DATABASE ootaka_sandbox_db;
USE SCHEMA public;
USE WAREHOUSE x_small_wh;
-- CREATE TABLE ... USING TEMPLATE
CREATE OR REPLACE TABLE region
USING TEMPLATE(
SELECT
ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
INFER_SCHEMA(
LOCATION => '@~/unload/region.parquet',
FILE_FORMAT => 'ootaka_parquet_format'
)
)
);
╒════════════════════════════════════╕
│ status │
╞════════════════════════════════════╡
│ Table REGION successfully created. │
╘════════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 1.738s
作成できました！テーブル定義も見てみましょう。
-- テーブル内容の表示 DESCRIBE TABLE ootaka_sandbox_db.public.region; ╒═════════════╤═══════════════════╤════════╤═══════╤═════════╤═════════════╤════════════╤═══════╤════════════╤═════════╤═════════════╕ │ name │ type │ kind │ null? │ default │ primary key │ unique key │ check │ expression │ comment │ policy name │ ╞═════════════╪═══════════════════╪════════╪═══════╪═════════╪═════════════╪════════════╪═══════╪════════════╪═════════╪═════════════╡ │ R_REGIONKEY │ NUMBER(38,0) │ COLUMN │ Y │ NULL │ N │ N │ NULL │ NULL │ NULL │ NULL │ ├─────────────┼───────────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤ │ R_NAME │ VARCHAR(16777216) │ COLUMN │ Y │ NULL │ N │ N │ NULL │ NULL │ NULL │ NULL │ ├─────────────┼───────────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤ │ R_COMMENT │ VARCHAR(16777216) │ COLUMN │ Y │ NULL │ N │ N │ NULL │ NULL │ NULL │ NULL │ ╘═════════════╧═══════════════════╧════════╧═══════╧═════════╧═════════════╧════════════╧═══════╧════════════╧═════════╧═════════════╛ 3 Row(s) produced. Time Elapsed: 0.117s
テーブル定義も良さそうですね。
TEMPLATEについて
ところで、TEMPLATEの内容が気になるので、こちらも少し確認してみます。
SELECT
ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
INFER_SCHEMA(
LOCATION => '@~/unload/region.parquet',
FILE_FORMAT => 'ootaka_parquet_format'
)
);
╒═══════════════════════════════════════════════════════════════════╕
│ ARRAY_AGG(OBJECT_CONSTRUCT(*)) │
╞═══════════════════════════════════════════════════════════════════╡
│ [ │
│ { │
│ "COLUMN_NAME": "R_REGIONKEY", │
│ "EXPRESSION": "$1:R_REGIONKEY::NUMBER(38, 0)", │
│ "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│ "NULLABLE": false, │
│ "TYPE": "NUMBER(38, 0)" │
│ }, │
│ { │
│ "COLUMN_NAME": "R_NAME", │
│ "EXPRESSION": "$1:R_NAME::TEXT", │
│ "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│ "NULLABLE": false, │
│ "TYPE": "TEXT" │
│ }, │
│ { │
│ "COLUMN_NAME": "R_COMMENT", │
│ "EXPRESSION": "$1:R_COMMENT::TEXT", │
│ "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│ "NULLABLE": true, │
│ "TYPE": "TEXT" │
│ } │
│ ] │
╘═══════════════════════════════════════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.975s
ということで、JSONの配列（ARRAY型）になっているんですね。そうすると、ARRAY型を渡せばテーブルが作成できそうな気もしたので試してみます。
-- TEMPLATEにARRAY型を渡してみる
CREATE OR REPLACE TABLE region_with_json_str
USING TEMPLATE(
SELECT
AS_ARRAY(
PARSE_JSON('
[
{
"COLUMN_NAME": "R_REGIONKEY",
"NULLABLE": false,
"TYPE": "NUMBER(38, 0)"
},
{
"COLUMN_NAME": "R_NAME",
"NULLABLE": false,
"TYPE": "TEXT"
}
]
')
)
);
000002 (0A000): Unsupported feature 'INFER_SCHEMA function must be used in the TEMPLATE sub-query'.
怒られてしまいました。TEMPLATEのサブクエリではINFER_SCHEMAを利用しないとダメだよ、ということですね。
ParquetファイルのデータをCOPYしてみる
データのCOPYについては、
テーブルを作成した後は、COPYコマンドでロードする際にmatch_by_column_nameオプションを指定してあげると、列名とparquetファイルの列名を自動でマッチングしてくれてロードできます！
とのことでしたので、最後にテーブル作成元にしたParquetファイルのCOPYもmatch_by_column_nameオプションを指定して試してみます。
-- ParquetファイルをCOPY COPY INTO ootaka_sandbox_db.public.region FROM '@~/unload/region.parquet' FILE_FORMAT = 'ootaka_parquet_format' MATCH_BY_COLUMN_NAME = CASE_SENSITIVE ; ╒════════════════════════════════════════════╤════════╤═════════════╤═════════════╤═════════════╤═════════════╤═════════════╤══════════════════╤═══════════════════════╤═════════════════════════╕ │ file │ status │ rows_parsed │ rows_loaded │ error_limit │ errors_seen │ first_error │ first_error_line │ first_error_character │ first_error_column_name │ ╞════════════════════════════════════════════╪════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╪══════════════════╪═══════════════════════╪═════════════════════════╡ │ unload/region.parquet_0_0_0.snappy.parquet │ LOADED │ 5 │ 5 │ 1 │ 0 │ NULL │ NULL │ NULL │ NULL │ ╘════════════════════════════════════════════╧════════╧═════════════╧═════════════╧═════════════╧═════════════╧═════════════╧══════════════════╧═══════════════════════╧═════════════════════════╛ 1 Row(s) produced. Time Elapsed: 2.105s
無事にCOPYできました！以下のように想定通りデータが入っています。
-- テーブルデータを確認 SELECT R_REGIONKEY, R_NAME, R_COMMENT FROM ootaka_sandbox_db.public.region ; ╒═════════════╤═════════════╤═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕ │ R_REGIONKEY │ R_NAME │ R_COMMENT │ ╞═════════════╪═════════════╪═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ 0 │ AFRICA │ lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to │ ├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 1 │ AMERICA │ hs use ironic, even requests. s │ ├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 2 │ ASIA │ ges. thinly even pinto beans ca │ ├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 3 │ EUROPE │ ly final courts cajole furiously final excuse │ ├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 4 │ MIDDLE EAST │ uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl │ ╘═════════════╧═════════════╧═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╛ 5 Row(s) produced. Time Elapsed: 1.256s
まとめ
以上、Snowflakeの「CREATE TABLE ... USING TEMPLATE」構文を試してみました。
この機能を利用すれば、スキーマ構造が不明なファイルが複数あった場合にも「テーブル作成」→「データロード」の流れをスムーズに実行することができそうです。こちらは2021年6月現在はまだPreview機能なのですが、正式リリースが待ち遠しい機能です。
どなたかのお役に立てば幸いです。それでは！
